import pymysql

host = "localhost"
port = 3306
user = "root"
password = "root"
database = "finance"




class DbUtils(object):
    def select(self,sql,param):
        db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        cursor = db.cursor()
        cursor.execute(sql,param)
        data = cursor.fetchall()
        cursor.close()
        db.close()
        return data
    def selectAll(self,sql):
        db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        cursor = db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        cursor.close()
        db.close()
        return data
    def update(self,sql,param):
        db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        cursor = db.cursor()
        cursor.execute(sql,param)
        db.commit()
        cursor.close()
        db.close()
        return True

    def deleDBZ(self,userID):
        param = []
        table = ["user_pay_money","user_change_money","user_fund_product","loan","flow_of_funds","bankcard"]
        for i in table:
            sql = "DELETE FROM {ta} WHERE userId = {userId};"
            sql = sql.format(ta = i,userId=str(userID))
            DbUtils().update(sql,param)
    def reCreate(self,name,pwd,paypwd):
        # 删除脏数据
        sql = "select id from user where username = %s"
        param = [name]
        if len(DbUtils().select(sql, param)) != 0:
            userID = DbUtils().select(sql, param)[0][0]
        else:
            userID = "null"
        DbUtils().deleDBZ(userID)
        # 删除该用户
        sql = "delete from user  where  username = %s"
        # sql = sql.format(user=table)
        param = [name]
        DbUtils().update(sql, param)

        # 重新创建该用户
        sql = "insert into user (username, password,paypwd) values (%s, md5(%s),%s)"
        # sql = sql.format(user=table)
        param = [name, pwd, paypwd]
        DbUtils().update(sql, param)
